import pymysql
# 创建类
# 1、导包
class DButil_sql:
    # 初始化
    conn = None
    cursor = None

# 2、创建连接，连接数据库
    @classmethod
    def __get_conn(cls):
        if cls.conn is None:
            cls.conn = pymysql.connect(user="root",  # The first four arguments is based on DB-API 2.0 recommendation.
            password="123456",
            host="localhost",
            database="books",
            port = 3306)
        return cls.conn


# 3、获取游标
    @classmethod
    def __get_cursor(cls):
        if cls.conn is None:
            cls.cursor = cls.__get_conn().cursor()
        return cls.cursor

# 4、执行sql
# cursor.execute(sql)
    @classmethod
    def exe_sql(cls,sql):
        try:
            # 需要获取游标
            cursor1 = cls.__get_cursor()
            cursor1.execute(sql)
            # 判断是否是查询还是非查询
            if sql.split()[0].lower() == "select":
                return cursor1.fetchall()
            else:
                # 提交事务，非查询的事务需要手动提交
                cls.conn.commit()
                return cursor1.rowcount
        except Exception as e:
            # 发生异常时，需要回滚
            cls.conn.rollback()
            # 将异常数据打印出来
            print(e)
        finally:
            cls.__close_conn()
            cls.__close_conn()

# 5关闭游标
    @classmethod
    def __close_cursor(cls):
        if cls.cursor:
            cls.cursor.close()
            cls.cursor = None

#6关闭连接
    @classmethod
    def __close_conn(cls):
        if cls.conn:
            cls.conn.close()
            cls.conn = None

